//
//  DataBase.m
//  NJUSTHelper
//
//  Created by 郑越辉 on 2017/9/18.
//  Copyright © 2017年 njust. All rights reserved.
//

#import "DataBase.h"
#import <FMDB.h>

static DataBase *_DBCtl = nil;
@interface DataBase()<NSCopying,NSMutableCopying>{
    FMDatabase  *_db;
    
}

@end
@implementation DataBase

-(id)copy{
    return self;
}

-(id)mutableCopy{
    return self;
}

-(id)copyWithZone:(NSZone *)zone{
    return self;
}

-(id)mutableCopyWithZone:(NSZone *)zone{
    return self;
}

+(instancetype)sharedDataBase{
    if (_DBCtl == nil) {
        _DBCtl = [[DataBase alloc] init];
        [_DBCtl initDataBase];
    }
    return _DBCtl;
}

+(instancetype)allocWithZone:(struct _NSZone *)zone{
    if (_DBCtl == nil) {
        _DBCtl = [super allocWithZone:zone];
    }
    return _DBCtl;
}

-(void)initDataBase{
    // 获得Documents目录路径
    
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"njust.sqlite"];
    
    // 实例化FMDataBase对象
    
    _db = [FMDatabase databaseWithPath:filePath];
    
    [_db open];
    
    // 初始化数据表
    NSString *userSql = @"CREATE TABLE  IF NOT EXISTS 'user' ('id' VARCHAR(16) PRIMARY KEY  NOT NULL ,'user_name' VARCHAR(40),'user_code' VARCHAR(20),'password' VARCHAR(40),'library_pwd' VARCHAR(40), 'is_login' INTEGER)";
    
    NSString *courseSql = @"CREATE TABLE  IF NOT EXISTS 'course' ('course_id' VARCHAR(40) PRIMARY KEY  NOT NULL ,'user_id' VARCHAR(16),'course_num' VARCHAR(20),'course_name' VARCHAR(40),'course_group' VARCHAR(40), 'teacher' VARCHAR(40), 'time_str' VARCHAR(128), 'place_str' VARCHAR(128), 'score' REAL, 'course_attr' VARCHAR(20), 'course_stage' VARCHAR(20))";
    ///table schedule 的字段time_str/place_str是用来显示课表的,与course不一样
    NSString *scheduleSql = @"CREATE TABLE  IF NOT EXISTS 'schedule' ('week_num' INTEGER, 'week_day' INTEGER, 'section_num' INTEGER, 'user_id' VARCHAR(16),'course_id' VARCHAR(40), 'course_num' VARCHAR(20),'course_name' VARCHAR(40),'course_group' VARCHAR(40), 'teacher' VARCHAR(40), 'time_str' VARCHAR(128), 'place_str' VARCHAR(128), 'score' REAL, 'course_attr' VARCHAR(20), 'course_stage' VARCHAR(20))";
    
    
    [_db executeUpdate:userSql];
    
    [_db executeUpdate:courseSql];
    
    [_db executeUpdate:scheduleSql];
    
    
    [_db close];
    
}


#pragma mark - user 接口

//判断用户是否存在
-(BOOL)checkUserIsExistWithUserId:(NSString *)userId{
    BOOL result = NO;
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"SELECT COUNT(id) AS countNum FROM user WHERE id = ?",userId];
    while ([rs next]) {
        if([rs intForColumn:@"countNum"] > 0){
            result = YES;
        }else{
            result = NO;
        }
    }
    [_db close];
    return result;
}
-(void)logoutAllUser{
    [_db open];
    [_db executeUpdate:@"UPDATE user SET is_login = ?", @(0)];
    [_db close];
}
//添加用户
-(NSInteger)addUser:(UserModel*)user {
    NSInteger result = 0;
    
    if([self checkUserIsExistWithUserId:user.userId]) {
        result = -1;
    }else {
        [_db open];
        result = [_db executeUpdate:@"INSERT INTO user(id, user_name, user_code, password, library_pwd, is_login) VALUES(?, ?, ?, ?, ?, ?)", user.userId, user.userName, user.userCode, user.password, user.libraryPwd, @(user.isLogin)];
        [_db close];
    }
    
    return result;
}
-(NSInteger)updateUser:(UserModel *)user{
    NSInteger result = 0;
    [_db open];
    
    result = [_db executeUpdate:@"UPDATE user SET user_name = ?, user_code = ?, password = ?, library_pwd = ?, is_login = ? WHERE id = ?", user.userName, user.userCode, user.password, user.libraryPwd, @(user.isLogin), user.userId];
    
    [_db close];
    return result;
}
//删除用户
-(NSInteger)deleteUser:(UserModel*)user {
    NSInteger result = 0;
    
    if(![self checkUserIsExistWithUserId:user.userId]){
        result = -1;
    }else {
        [_db open];
        result = [_db executeUpdate:@"DELETE FROM user WHERE id = ?", user.userId];
        [_db close];
    }
    
    return result;
}
//查询所有用户
-(NSMutableArray *)selectAllUser {
    NSMutableArray *result = [[NSMutableArray alloc]init];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"SELECT * FROM user"];
    while ([rs next]) {
        UserModel *user = [[UserModel alloc]init];
        user.userId = [rs stringForColumn:@"id"];
        user.userName = [rs stringForColumn:@"user_name"];
        user.userCode = [rs stringForColumn:@"user_code"];
        user.password = [rs stringForColumn:@"password"];
        user.libraryPwd = [rs stringForColumn:@"library_pwd"];
        user.isLogin = [[rs stringForColumn:@"is_login"] integerValue];
        [result addObject:user];
    }
    [_db close];
    return result;
}
//根据Id查询用户
-(UserModel *)selectUserByUserId:(NSString *)userId {
    UserModel *result = [[UserModel alloc]init];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"SELECT * FROM user WHERE id = ?", userId];
    if ([rs next]) {
        result.userId = [rs stringForColumn:@"id"];
        result.userName = [rs stringForColumn:@"user_name"];
        result.userCode = [rs stringForColumn:@"user_code"];
        result.password = [rs stringForColumn:@"password"];
        result.libraryPwd = [rs stringForColumn:@"library_pwd"];
        result.isLogin = [[rs stringForColumn:@"is_login"] integerValue];
    }
    [_db close];
    return result;
}
//查询登录用户
-(UserModel *)selectLoginUser{
    UserModel *result = [[UserModel alloc]init];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"SELECT * FROM user WHERE is_login = ?", @(1)];
    if ([rs next]) {
        result.userId = [rs stringForColumn:@"id"];
        result.userName = [rs stringForColumn:@"user_name"];
        result.userCode = [rs stringForColumn:@"user_code"];
        result.password = [rs stringForColumn:@"password"];
        result.libraryPwd = [rs stringForColumn:@"library_pwd"];
        result.isLogin = [[rs stringForColumn:@"is_login"] integerValue];
    }
    [_db close];
    return result;
}
//更新教务处密码
-(NSInteger)updatePasswordWithUser:(UserModel *)user{
    NSInteger result = 0;
    [_db open];
    if(![self checkUserIsExistWithUserId:user.userId]){
        result = -1;
    }else {
        result = [_db executeUpdate:@"UPDATE user SET password = ? WHERE id = ?", user.password, user.userId];
    }
    [_db close];
    return result;
}
//更新图书馆密码
-(NSInteger)updateLibraryPwdWithUser:(UserModel *)user{
    NSInteger result = 0;
    [_db open];
    if(![self checkUserIsExistWithUserId:user.userId]){
        result = -1;
    }else {
        result = [_db executeUpdate:@"UPDATE user SET library_pwd = ? WHERE id = ?", user.libraryPwd, user.userId];
    }
    [_db close];
    return result;
}

#pragma mark --- course 接口 --
-(NSInteger)removeAllCourse4UserId:(NSString*)userId{
    NSInteger result = 0;
    [_db open];
    
    result = [_db executeUpdate:@"delete from course where user_id = ?", userId];
    
    [_db close];
    return result;
    
}

-(NSInteger)addCoursesWithArray:(NSMutableArray *)courseArray{
    NSInteger result = 0;
    [_db open];
    for (CourseModel *course in courseArray) {
        if([_db executeUpdate:@"INSERT INTO course (course_id, user_id, course_num, course_name, course_group, teacher, time_str, place_str, score, course_attr, course_stage) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", course.courseId, course.userId, course.courseNum, course.courseName, course.courseGroup, course.teacher, course.timeStr, course.placeStr, @(course.score), course.courseAttr, course.courseStage]){
            result++;
        }
    }
    [_db close];
    return result;
}


-(NSMutableArray *)getAllCourseByUserId:(NSString *)userId{
    NSMutableArray * result = [NSMutableArray array];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"select * from course where user_id = ?", userId];
    while ([rs next]) {
        CourseModel *course = [[CourseModel alloc]init];
        course.courseId = [rs stringForColumn:@"course_id"];
        course.userId = [rs stringForColumn:@"user_id"];
        course.courseNum = [rs stringForColumn:@"course_num"];
        course.courseName = [rs stringForColumn:@"course_name"];
        course.courseGroup = [rs stringForColumn:@"course_group"];
        course.teacher = [rs stringForColumn:@"teacher"];
        course.timeStr = [rs stringForColumn:@"time_str"];
        course.placeStr = [rs stringForColumn:@"place_str"];
        course.score = [[rs stringForColumn:@"score"] floatValue];
        course.courseAttr = [rs stringForColumn:@"course_attr"];
        course.courseStage = [rs stringForColumn:@"course_stage"];
        [result addObject:course];
    }
    [_db close];
    return result;
}

#pragma mark --- schedule 接口 --

-(NSInteger)removeAllSchedule4UserId:(NSString*)userId{
    NSInteger result = 0;
    [_db open];
    
    result = [_db executeUpdate:@"delete from schedule where user_id = ?", userId];
    
    [_db close];
    return result;
    
}

-(NSInteger)addSchedulesWithArray:(NSMutableArray *)scheduleArray{
    NSInteger result = 0;
    [_db open];
    for (ScheduleModel *schedule in scheduleArray) {
        if(schedule.weekNum == 0) {
            continue;
        }
        if([_db executeUpdate:@"INSERT INTO schedule (week_num, week_day, section_num, course_id, user_id, course_num, course_name, course_group, teacher, time_str, place_str, score, course_attr, course_stage) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",@(schedule.weekNum), @(schedule.weekDay), @(schedule.sectionNum), schedule.courseId, schedule.userId, schedule.courseNum, schedule.courseName, schedule.courseGroup, schedule.teacher, schedule.timeStr, schedule.placeStr, @(schedule.score), schedule.courseAttr, schedule.courseStage]){
            result++;
        }
    }
    [_db close];
    return result;
}


-(NSMutableArray *)getAllScheduleByUserId:(NSString *)userId{
    NSMutableArray * result = [NSMutableArray array];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"select * from schedule where user_id = ?", userId];
    while ([rs next]) {
        ScheduleModel *schedule = [[ScheduleModel alloc]init];
        schedule.weekNum = [[rs stringForColumn:@"week_num"] integerValue];
        schedule.weekDay = [[rs stringForColumn:@"week_day"] integerValue];
        schedule.sectionNum = [[rs stringForColumn:@"section_num"] integerValue];
        schedule.courseId = [rs stringForColumn:@"course_id"];
        schedule.userId = [rs stringForColumn:@"user_id"];
        schedule.courseNum = [rs stringForColumn:@"course_num"];
        schedule.courseName = [rs stringForColumn:@"course_name"];
        schedule.courseGroup = [rs stringForColumn:@"course_group"];
        schedule.teacher = [rs stringForColumn:@"teacher"];
        schedule.timeStr = [rs stringForColumn:@"time_str"];
        schedule.placeStr = [rs stringForColumn:@"place_str"];
        schedule.score = [[rs stringForColumn:@"score"] floatValue];
        schedule.courseAttr = [rs stringForColumn:@"course_attr"];
        schedule.courseStage = [rs stringForColumn:@"course_stage"];
        [result addObject:schedule];
    }
    [_db close];
    return result;
}

-(NSMutableArray *)getScheduleByWeekDay:(NSInteger)weekDay UserId:(NSString *)userId{
    NSMutableArray * result = [NSMutableArray array];
    [_db open];
    FMResultSet *rs = [_db executeQuery:@"select * from schedule where user_id = ? and week_day = ?", userId, @(weekDay)];
    while ([rs next]) {
        ScheduleModel *schedule = [[ScheduleModel alloc]init];
        schedule.weekNum = [[rs stringForColumn:@"week_num"] integerValue];
        schedule.weekDay = [[rs stringForColumn:@"week_day"] integerValue];
        schedule.sectionNum = [[rs stringForColumn:@"section_num"] integerValue];
        schedule.courseId = [rs stringForColumn:@"course_id"];
        schedule.userId = [rs stringForColumn:@"user_id"];
        schedule.courseNum = [rs stringForColumn:@"course_num"];
        schedule.courseName = [rs stringForColumn:@"course_name"];
        schedule.courseGroup = [rs stringForColumn:@"course_group"];
        schedule.teacher = [rs stringForColumn:@"teacher"];
        schedule.timeStr = [rs stringForColumn:@"time_str"];
        schedule.placeStr = [rs stringForColumn:@"place_str"];
        schedule.score = [[rs stringForColumn:@"score"] floatValue];
        schedule.courseAttr = [rs stringForColumn:@"course_attr"];
        schedule.courseStage = [rs stringForColumn:@"course_stage"];
        [result addObject:schedule];
    }
    [_db close];
    return result;
}





@end
